from urllib.parse import quote

import xlwt
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render

from app01.models import 其他合同
from app01.utils.fieldshtml import fields_to_html, fields_to_save
from app01.utils.pagination import Pagination
from django.core.paginator import Paginator
from django.utils.safestring import mark_safe

# 其他合同列表
def contract_other_list(request):
    condtions = {}
    jsyf = request.GET.get('jsyf', '')  # 结算月份
    if jsyf != '':
        condtions['结算月份__istartswith'] = jsyf

    xmmc = request.GET.get('xmmc', '')  # 项目名称
    if xmmc != '':
        condtions['项目名称__icontains'] = xmmc

    fylb = request.GET.get('fylb', '')  # 费用类别
    if fylb != '':
        condtions['费用类别'] = fylb

    zxlb = request.GET.get('zxlb', '')  # 专项类别
    if zxlb != '':
        condtions['专项类别'] = zxlb

    gys = request.GET.get('gys', '')  # 供应商
    if gys != '':
        condtions['供应商__icontains'] = gys

    # 依据条件查询记录
    contract_other_list_all = 其他合同.objects.filter(**condtions).order_by('-结算月份', '-序号')
    # 计算全部数据的合计数
    sum_all = contract_other_list_all.aggregate(Count('项目名称'), Sum('合同金额'), Sum('结算金额'), Sum('奖励金额'), Sum('考核扣款'),
                                 Sum('能源费'), Sum('bggyjhba'), Sum('bggyjhca'), Sum('bggyjhcb'), Sum('bggyjhcc'),
                                 Sum('bggyjhce'), Sum('bggyjhcf1'), Sum('bggyjhcf2'), Sum('bggyjhcg'), Sum('bggyjhch'),
                                 Sum('bggyjhcj'), Sum('bggyjhcs'), Sum('bggyjhcm'), Sum('bggyjhcn'), Sum('bggyjhfa'),
                                 Sum('bggyjhfb'), Sum('bggyjhfc'), Sum('bggyjhfj'), Sum('bggyjhfn'), Sum('bggyjhfp'),
                                 Sum('bggyjhga'), Sum('bggyjhgb'), Sum('bggyjhia'), Sum('厂部及其他'))

    # 将数字转换成千分位表示
    for key, value in sum_all.items():
        sum_all[key] = '{:,}'.format(value)

    # contract_other_list_all = 其他合同.objects.all().order_by('-结算月份', '-序号')
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str, dicts = fields_to_html(request.session['info']['employeeid'], 其他合同)

    # 生成分页HTML代码
    # page = Pagination(request, contract_other_list_all)

    pageA = request.GET.get('page', 1)  # 获取指定页面
    pagesize = request.GET.get('pagesize', 10)  # 每页有多少条数据
    paginator = Paginator(contract_other_list_all, pagesize)
    page_1 = paginator.get_page(pageA)  # 获取指定页面数据

    # 计算当前页数据合计数
    sum_row = {}
    sum_row['项目名称__count'] = 0
    sum_row['合同金额__sum'] = 0
    sum_row['结算金额__sum'] = 0
    sum_row['奖励金额__sum'] = 0
    sum_row['考核扣款__sum'] = 0
    sum_row['能源费__sum'] = 0
    sum_row['bggyjhba__sum'] = 0
    sum_row['bggyjhca__sum'] = 0
    sum_row['bggyjhcb__sum'] = 0
    sum_row['bggyjhcc__sum'] = 0
    sum_row['bggyjhce__sum'] = 0
    sum_row['bggyjhcf1__sum'] = 0
    sum_row['bggyjhcf2__sum'] = 0
    sum_row['bggyjhcg__sum'] = 0
    sum_row['bggyjhch__sum'] = 0
    sum_row['bggyjhcj__sum'] = 0
    sum_row['bggyjhcs__sum'] = 0
    sum_row['bggyjhcm__sum'] = 0
    sum_row['bggyjhcn__sum'] = 0
    sum_row['bggyjhfa__sum'] = 0
    sum_row['bggyjhfb__sum'] = 0
    sum_row['bggyjhfc__sum'] = 0
    sum_row['bggyjhfj__sum'] = 0
    sum_row['bggyjhfn__sum'] = 0
    sum_row['bggyjhfp__sum'] = 0
    sum_row['bggyjhga__sum'] = 0
    sum_row['bggyjhgb__sum'] = 0
    sum_row['bggyjhia__sum'] = 0
    sum_row['厂部及其他__sum'] = 0
    for row in page_1.object_list:
        # print(row.实发数量, row.实发领用金额)
        sum_row['项目名称__count'] += 1
        sum_row['合同金额__sum'] += 0 if row.合同金额 is None else row.合同金额
        sum_row['结算金额__sum'] += 0 if row.结算金额 is None else row.结算金额
        sum_row['奖励金额__sum'] += 0 if row.奖励金额 is None else row.奖励金额
        sum_row['考核扣款__sum'] += 0 if row.考核扣款 is None else row.考核扣款
        sum_row['能源费__sum'] += 0 if row.能源费 is None else row.能源费
        sum_row['bggyjhba__sum'] += 0 if row.bggyjhba is None else row.bggyjhba
        sum_row['bggyjhca__sum'] += 0 if row.bggyjhca is None else row.bggyjhca
        sum_row['bggyjhcb__sum'] += 0 if row.bggyjhcb is None else row.bggyjhcb
        sum_row['bggyjhcc__sum'] += 0 if row.bggyjhcc is None else row.bggyjhcc
        sum_row['bggyjhce__sum'] += 0 if row.bggyjhce is None else row.bggyjhce
        sum_row['bggyjhcf1__sum'] += 0 if row.bggyjhcf1 is None else row.bggyjhcf1
        sum_row['bggyjhcf2__sum'] += 0 if row.bggyjhcf2 is None else row.bggyjhcf2
        sum_row['bggyjhcg__sum'] += 0 if row.bggyjhcg is None else row.bggyjhcg
        sum_row['bggyjhch__sum'] += 0 if row.bggyjhch is None else row.bggyjhch
        sum_row['bggyjhcj__sum'] += 0 if row.bggyjhcj is None else row.bggyjhcj
        sum_row['bggyjhcs__sum'] += 0 if row.bggyjhcs is None else row.bggyjhcs
        sum_row['bggyjhcm__sum'] += 0 if row.bggyjhcm is None else row.bggyjhcm
        sum_row['bggyjhcn__sum'] += 0 if row.bggyjhcn is None else row.bggyjhcn
        sum_row['bggyjhfa__sum'] += 0 if row.bggyjhfa is None else row.bggyjhfa
        sum_row['bggyjhfb__sum'] += 0 if row.bggyjhfb is None else row.bggyjhfb
        sum_row['bggyjhfc__sum'] += 0 if row.bggyjhfc is None else row.bggyjhfc
        sum_row['bggyjhfj__sum'] += 0 if row.bggyjhfj is None else row.bggyjhfj
        sum_row['bggyjhfn__sum'] += 0 if row.bggyjhfn is None else row.bggyjhfn
        sum_row['bggyjhfp__sum'] += 0 if row.bggyjhfp is None else row.bggyjhfp
        sum_row['bggyjhga__sum'] += 0 if row.bggyjhga is None else row.bggyjhga
        sum_row['bggyjhgb__sum'] += 0 if row.bggyjhgb is None else row.bggyjhgb
        sum_row['bggyjhia__sum'] += 0 if row.bggyjhia is None else row.bggyjhia
        sum_row['厂部及其他__sum'] += 0 if row.厂部及其他 is None else row.厂部及其他

    # 将数字转换成千分位表示
    for key, value in sum_row.items():
        sum_row[key] = '{:,}'.format(value)

    context = {
        'sum_row': sum_row,
        'sum_all': sum_all,     # 全部数据合计数
        'fields': dicts,
        'select': select_str,
        'active2': 'active',
        'title': '其他合同查询',
        'select_jsyf': select_jsyf(jsyf),  # 筛选条件下拉数据
        'select_zxlb': select_zxlb(zxlb),  # 筛选条件下拉数据
        'select_fylb': select_fylb(fylb),  # 筛选条件下拉数据
        'page_1': page_1,  # 要显示的页面数据
        'jsyf': jsyf,
        'xmmc': xmmc,
        'fylb': fylb,
        'zxlb': zxlb,
        'gys': gys,
    }
    return render(request, 'contract_other_list.html', context)


def contract_other_fields(request):
    fields_str = request.GET.get('f')
    sel_fields = fields_str.split(',')
    # print(type(sel_fields))
    fields_to_save(request.session['info']['employeeid'], 其他合同, sel_fields)
    return JsonResponse({'status': True})

def export_excel(request):
    condtions = {}
    jsyf = request.GET.get('jsyf', '')  # 结算月份
    if jsyf != '':
        condtions['结算月份__istartswith'] = jsyf

    xmmc = request.GET.get('xmmc', '')  # 项目名称
    if xmmc != '':
        condtions['项目名称__icontains'] = xmmc

    fylb = request.GET.get('fylb', '')  # 费用类别
    if fylb != '':
        condtions['费用类别'] = fylb

    zxlb = request.GET.get('zxlb', '')  # 专项类别
    if zxlb != '':
        condtions['专项类别'] = zxlb

    gys = request.GET.get('gys', '')  # 供应商
    if gys != '':
        condtions['供应商__icontains'] = gys

    # 依据条件查询记录
    contract_other_list_all = 其他合同.objects.filter(**condtions).order_by('-结算月份', '-序号')

    filename = quote('其他合同.xls')
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    fields = list(contract_other_list_all.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, contract_other_list_all.model._meta.get_field(title).verbose_name)

    row_index = 1
    for row in contract_other_list_all.values():
        # print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response

def select_jsyf(jsyf):
    rows = 其他合同.objects.values('结算月份').distinct().order_by('-结算月份')
    select_str_list = ['<select id="jsyf" name="jsyf" class="form-control">']
    select_str_list.append('<option value=""></option>')
    js_year = ''
    for v in rows:
        if v['结算月份'][:4] != js_year:
            js_year = v['结算月份'][:4]
            if js_year == jsyf:
                select_str_list.append(
                    '<option value="{}" selected="selected">{}</option>'.format(js_year, js_year))
            else:
                select_str_list.append('<option value="{}">{}</option>'.format(js_year, js_year))

        if v['结算月份'] == jsyf:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['结算月份'], v['结算月份']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['结算月份'], v['结算月份']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)

def select_zxlb(zxlb):
    rows = 其他合同.objects.values('专项类别').distinct().order_by('专项类别')
    select_str_list = ['<select id="zxlb" name="zxlb" class="form-control">']
    select_str_list.append('<option value=""></option>')
    for v in rows:
        if v['专项类别'] == zxlb:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['专项类别'], v['专项类别']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['专项类别'], v['专项类别']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)


def select_fylb(fylb):
    rows = 其他合同.objects.values('费用类别').distinct().order_by('费用类别')
    select_str_list = ['<select id="fylb" name="fylb" class="form-control">']
    select_str_list.append('<option value=""></option>')
    for v in rows:
        if v['费用类别'] == fylb:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['费用类别'], v['费用类别']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['费用类别'], v['费用类别']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)
